package com.example.demo.excel_utu;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFReader.SheetIterator;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class Excel2007Reader extends DefaultHandler {

    private enum XSSFDataType {
        BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER,
    }

    //共享字符串表
    private SharedStringsTable sst;

    private List<String> rowlist = new ArrayList<>();

    private StylesTable stylesTable;

    private boolean vIsOpen = false;

    private StringBuffer value = new StringBuffer();

    private XSSFDataType nextDataType;

    // 当前遍历的excel单元格列索引
    protected int formatIndex;

    protected String formatString;

    private int curCol = 0; // 当前列索引

    private int preCol = 0; // 上一列列索引

    private final DataFormatter formatter = new DataFormatter();

    private List<List<String>> rsList = new ArrayList<>();

    private HashMap<String, List<List<String>>> sheetData = new HashMap<>();


    /**
     * 只遍历一个电子表格， 其中sheetID为要遍历的sheet索引，从1开始，1-3
     *
     * @param filename
     * @param sheetId
     * @throws Exception
     */
    public void process(String filename, int sheetId) throws Exception {
        OPCPackage pkg = OPCPackage.open(filename);
        XSSFReader r = new XSSFReader(pkg);
        this.stylesTable = r.getStylesTable();
        SharedStringsTable sst = r.getSharedStringsTable();
        XMLReader parser = fetchSheetParser(sst);

        // 根据 rId 查找sheet
        InputStream sheet2 = r.getSheet("rId" + sheetId);
        InputSource sheetSource = new InputSource(sheet2);
        parser.parse(sheetSource);
        sheetData.put("rId"+sheetId, rsList);
        sheet2.close();
        pkg.revert();
    }

    /**
     * 遍历工作簿中所有的电子表格
     *
     * @param filename
     * @param sheetName
     * @throws Exception
     */
    public void process(String filename, String sheetName) throws Exception {
        if (StringUtils.isBlank(sheetName)){
            return;
        }
        OPCPackage pkg = OPCPackage.open(filename);
        XSSFReader r = new XSSFReader(pkg);
        this.stylesTable = r.getStylesTable();
        SharedStringsTable sst = r.getSharedStringsTable();
        XMLReader parser = fetchSheetParser(sst);
        SheetIterator sheets = (SheetIterator)r.getSheetsData();

        while (sheets.hasNext()){
            InputStream sheet = sheets.next();
            String name = sheets.getSheetName();
            if (sheetName.equals(name)){
                InputSource sheetSource = new InputSource(sheet);
                parser.parse(sheetSource);
                sheetData.put(sheetName,rsList);
                sheet.close();
                break;
            }
            sheet.close();
        }
        pkg.revert();
    }

    /**
     * 遍历工作簿中所有的电子表格(获取多Sheet数据)
     *
     * @param filename
     * @param sheetNames
     * @throws Exception
     */
    public void process(String filename, List<String> sheetNames) throws Exception {
        if (sheetNames == null || sheetNames.size() == 0){
            return;
        }
        OPCPackage pkg = OPCPackage.open(filename);
        XSSFReader r = new XSSFReader(pkg);
        this.stylesTable = r.getStylesTable();
        SharedStringsTable sst = r.getSharedStringsTable();
        XMLReader parser = fetchSheetParser(sst);
        SheetIterator sheets = (SheetIterator)r.getSheetsData();

        while (sheets.hasNext()){
            InputStream sheet = sheets.next();
            String name = sheets.getSheetName();
            if (sheetNames.contains(name)){
                InputSource sheetSource = new InputSource(sheet);
                parser.parse(sheetSource);
                //sheet.close();
                //break;
                sheetData.put(name, this.rsList);
                this.rsList = new ArrayList<>();
            }
            sheet.close();
        }
        pkg.revert();
    }

    /**
     * 遍历工作簿中所有的电子表格(数据流方式)
     *
     * @param is
     * @param sheetName
     * @throws Exception
     */
    public void process(InputStream is, String sheetName) throws Exception {
        if (StringUtils.isBlank(sheetName)){
            return;
        }
        OPCPackage pkg = OPCPackage.open(is);
        XSSFReader r = new XSSFReader(pkg);
        this.stylesTable = r.getStylesTable();
        SharedStringsTable sst = r.getSharedStringsTable();
        XMLReader parser = fetchSheetParser(sst);
        SheetIterator sheets = (SheetIterator)r.getSheetsData();

        while (sheets.hasNext()){
            InputStream sheet = sheets.next();
            String name = sheets.getSheetName();
            if (sheetName.equals(name)){
                InputSource sheetSource = new InputSource(sheet);
                parser.parse(sheetSource);
                sheetData.put(sheetName,rsList);
                sheet.close();
                break;
            }
            sheet.close();
        }
        pkg.revert();
    }


    /**
     * 遍历工作簿中所有的电子表格(数据流方式， 获取多Sheet数据)
     *
     * @param is
     * @param sheetNames
     * @throws Exception
     */
    public void process(InputStream is, List<String> sheetNames) throws Exception {
        if (sheetNames == null || sheetNames.size() == 0){
            return;
        }
        OPCPackage pkg = OPCPackage.open(is);
        XSSFReader r = new XSSFReader(pkg);
        this.stylesTable = r.getStylesTable();
        SharedStringsTable sst = r.getSharedStringsTable();
        XMLReader parser = fetchSheetParser(sst);
        SheetIterator sheets = (SheetIterator)r.getSheetsData();

        while (sheets.hasNext()){
            InputStream sheet = sheets.next();
            String name = sheets.getSheetName();
            if (sheetNames.contains(name)){
                InputSource sheetSource = new InputSource(sheet);
                parser.parse(sheetSource);
                //sheet.close();
                //break;
                sheetData.put(name, this.rsList);
                this.rsList = new ArrayList<>();
            }
            sheet.close();
        }
        pkg.revert();
    }

    public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
        XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
        this.sst = sst;
        parser.setContentHandler(this);
        return parser;
    }

    @Override
    public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
        // c => 单元格
        if ("inlineStr".equals(name) || "v".equals(name) || "t".equals(name)) {
            vIsOpen = true;
            value.setLength(0);
        }
        if ("c".equals(name)) {
            // 如果下一个元素是SST的索引，则将nextIsString标记为true
            String cellType = attributes.getValue("t");
            this.nextDataType = XSSFDataType.NUMBER;

            this.formatIndex = -1;
            this.formatString = null;
            // 日期格式
            String cellStyleStr = attributes.getValue("s");
            String rowStr = attributes.getValue("r");
            curCol = this.getRowIndex(rowStr);
            if ("b".equals(cellType))
                nextDataType = XSSFDataType.BOOL;
            else if ("e".equals(cellType))
                nextDataType = XSSFDataType.ERROR;
            else if ("inlineStr".equals(cellType))
                nextDataType = XSSFDataType.INLINESTR;
            else if ("s".equals(cellType))
                nextDataType = XSSFDataType.SSTINDEX;
            else if ("str".equals(cellType))
                nextDataType = XSSFDataType.FORMULA;
            else if (cellStyleStr != null) {
                int styleIndex = Integer.parseInt(cellStyleStr);
                XSSFCellStyle style = this.stylesTable.getStyleAt(styleIndex);
                this.formatIndex = style.getDataFormat();
                this.formatString = style.getDataFormatString();
                if (this.formatString == null)
                    this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
            }
        }
    }

    @Override
    public void endElement(String uri, String localNmae, String name) throws SAXException {
        Object thisObj = null;
        if ("v".equals(name) || "t".equals(name)) {
            // 当前单元格数据
            thisObj = extractCellValue(value, nextDataType);
            int cols = curCol - preCol;
            if (cols > 1){
                for (int i = 0; i < cols - 1; i++) {
                    rowlist.add(preCol, "");
                }
            }
            preCol = curCol;
            rowlist.add(curCol - 1, thisObj == null ? "" : thisObj.toString());
        } else if ("row".equals(name)){
            addRowList();
            rowlist.clear();
            curCol = 0;
            preCol = 0;
        }
    }

    /**
     * 抽取单元格数据
     *
     * @param value
     * @param nextDataType
     * @return
     */
    private Object extractCellValue(StringBuffer value, XSSFDataType nextDataType) {
        Object obj = "";
        switch (nextDataType) {
            case BOOL:
                char first = value.charAt(0);
                obj = first == '0' ? false : true;
                break;
            case ERROR:
                obj = value.toString().trim();
                break;
            case FORMULA:
                obj = value.toString().trim();
                break;
            case INLINESTR:
                XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
                obj = rtsi.toString().trim();
                break;
            case SSTINDEX:
                String sstIndex = value.toString().trim();
                try {
                    int idx = Integer.parseInt(sstIndex);
                    XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));
                    obj = rtss.toString().trim();
                } catch (NumberFormatException ex) {
                    ex.printStackTrace();
                }
                break;
            case NUMBER:
                String n = value.toString().trim();
                if (this.formatString != null && formatIndex == 14) {
                    obj = formatter.formatRawCellContents(Double.parseDouble(n), this.formatIndex, this.formatString);
                } else {
                    obj = n;
                }
                break;
            default:
                obj = "";
                break;
        }
        return obj;
    }

    @Override
    public void characters(char[] ch, int start, int length) throws SAXException {
        if (vIsOpen) {
            value.append(ch, start, length);
        }
    }

    private void addRowList(){
        if (rowlist.size() > 0){
            List<String> tempList = new ArrayList<>();
            tempList.addAll(rowlist);
            rsList.add(tempList);
        }
    }

    public List<List<String>> getRsList(){
        return this.rsList;
    }

    public HashMap<String, List<List<String>>> getSheetData(){
        return this.sheetData;
    }

    public void setRsList(List<List<String>> rsList){
        this.rsList = rsList;
    }

    private int getRowIndex(String rowStr) {
        rowStr = rowStr.replaceAll("[^A-Z]", "");
        byte[] rowAbc = rowStr.getBytes();
        int len = rowAbc.length;
        float num = 0;
        for (int i = 0; i < len; i++) {
            num += (rowAbc[i] - 'A' + 1) * Math.pow(26, len - i - 1);
        }
        return (int) num;
    }

}
